import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import result_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def result_wxycyjzjjg(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(result_bucket,table_name, date)
    sql = f'''insert into fridge_biz_result.result_wxycyjzjjg
                (
                select
                    sn,
                    upper(if(t4.model is not null,t4.model,regexp_extract(ms,'[a-zA-Z0-9]+-[a-zA-Z0-9]+') )) as model,
                    if(fault is null,'',fault) as fault,
                    fault_repair_quantity,
                    total_repair_quantity,
                    format_datetime(date('{date}') - interval '1' month, 'YYYY-MM') as time
                from
                (
                    select
                        if(t1.sn is null,t2.sn,t1.sn) as sn,
                        if(t2.ms is null,t1.ms,t2.ms) as ms,
                        t1.fault,
                        t1.fault_repair_quantity,
                        t2.total_repair_quantity
                    from
                    (
                        select
                            if(zzfld00000b is null,'',zzfld00000b) as sn ,
                            if(zzfld00002gt is null,'',zzfld00002gt) as fault,
                            max(zzfld000036) as ms,
                            count(distinct objectid) as fault_repair_quantity
                        FROM
                            ods.kyd_ods
                        where
                            date(p_date)>=(date('{date}')  - interval '6' month)
                        and
                            date(p_date) < date('{date}') 
                        and
                            zzfld000001='QT30'
                        and
                            zzfld00002ft != '安装'
                        and
                            zzfld00000b is not null and zzfld00000b != ''
                        group by
                            if(zzfld00000b is null,'',zzfld00000b),
                            if(zzfld00002gt is null,'',zzfld00002gt)
                        having
                            count(distinct objectid) >= 3
                    ) t1
                        full join
                    (
                        select
                            if(zzfld00000b is null,'',zzfld00000b) as sn ,
                            max(zzfld000036) as ms,
                            count(distinct objectid) as total_repair_quantity
                        FROM
                            ods.kyd_ods
                        where
                            date(p_date)>=(date('{date}')  - interval '12' month)
                        and
                            date(p_date) < date('{date}') 
                        and
                            zzfld000001='QT30'
                        and
                            zzfld00002ft != '安装'
                        and
                            zzfld00000b is not null and zzfld00000b != ''
                        group by
                            if(zzfld00000b is null,'',zzfld00000b)
                        having
                            count(distinct objectid) >= 5
                    ) t2
                    on t1.sn = t2.sn
                ) t3
                left join
                    dim.material t4
                on
                    substr(t3.sn,4,7) = t4.material and type_code = 'FRIDGE'
                )
                 '''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        result_wxycyjzjjg(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)

